﻿CREATE PROCEDURE [dbo].[friends_get_all]
    @userId int,
    @page int = 0,
    @size int = 0,
    @column nvarchar(256),
    @direction int = 0,
    @email nvarchar(256) = null,
    @login nvarchar(256) = null

AS
    SELECT
        Users.Avatar,
        Users.Email,
        Users.Login,
        Users.UserId
    FROM Users, Friends
    WHERE (Friends.FriendId=@userId and Users.UserId = Friends.UserId and Friends.IsAccepted = 0)
        and (@email IS NULL or Users.Email LIKE '%' + @email  +'%' )
        and (@login IS NULL or Users.Login LIKE '%' + @login + '%')
    ORDER BY
        CASE WHEN @column = 'Email' and @direction = 0 THEN Users.Email END ASC,
        CASE WHEN @column = 'Email' and @direction = 1 THEN Users.Email END DESC,
        CASE WHEN @column = 'Login' and @direction = 0 THEN Users.Login END ASC,
        CASE WHEN @column = 'Login' and @direction = 1 THEN Users.Login END DESC
    
        OFFSET ((@page-1)*@size) ROWS
        FETCH NEXT @size ROWS ONLY

DECLARE @TotalCount int
    SELECT @TotalCount = Count(Users.Login)
    FROM Users,Friends
    WHERE (Friends.UserId=@userId and Friends.IsAccepted = 0)
        and (@email IS NULL or Users.Email LIKE '%' + @email  +'%' )
        and (@login IS NULL or Users.Login LIKE '%' + @login + '%')
        
RETURN @TotalCount
